﻿using System;
using System.Collections.ObjectModel;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using System.IO;
using System.Xml.Serialization;

namespace WindowsFormsApplication6
{
    public class CustomProvider:DbSyncProvider
    {
        public CustomProvider(string scopeName, Sync.Library.Tables tables)
        {
            this.Connection = new SqlConnection(Sync.Library.Settings.ConnStringMain);

            this.ScopeName = scopeName;
            LoadScopeCongif(scopeName);

            foreach (Sync.Library.Table t in tables)
            {
                this.SyncAdapters.Add(CreateAdapter(t));
            }

            LoadSelectNewTimestampCommand();
            LoadSelectOverlappingScopesCommand();
            LoadSelectScopeInfoCommand();
            LoadSelectTableMaxTimestampsCommand();
            LoadUpdateScopeCleanupTimestampCommand();
            LoadUpdateScopeInfoCommand();

        }

        private void LoadSelectScopeInfoCommand()
        {
            SqlCommand selReplicaInfoCmd = new SqlCommand();
            selReplicaInfoCmd.CommandType = CommandType.Text;

            //DbSyncSession.SyncScopeId
            //DbSyncSession.SyncScopeLocalId
            //DbSyncSession.SyncScopeKnowledge
            //DbSyncSession.SyncScopeCleanupKnowledge
            //DbSyncSession.SyncScopeTimestamp
            
            selReplicaInfoCmd.CommandText = "SELECT " +
                                            "scope_id, " +
                                            "scope_local_id, " +
                                            "scope_sync_knowledge, " +
                                            "scope_tombstone_cleanup_knowledge, " +
                                            "scope_timestamp " +
                                            "FROM scope_info " +
                                            "WHERE scope_name = @" + DbSyncSession.SyncScopeName;
            selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);

            this.SelectScopeInfoCommand = selReplicaInfoCmd;

        }

        private void LoadUpdateScopeCleanupTimestampCommand()
        {
            SqlCommand updScopeCleanupInfoCmd = new SqlCommand();
            updScopeCleanupInfoCmd.CommandType = CommandType.Text;
            updScopeCleanupInfoCmd.CommandText = "UPDATE  scope_info set " +
                                                 " scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp +
                                                 " WHERE scope_name = @" + DbSyncSession.SyncScopeName +
                                                 " AND(scope_cleanup_timestamp is null or scope_cleanup_timestamp <  @" + DbSyncSession.SyncScopeCleanupTimestamp + ");" +
                                                 " SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
            updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupTimestamp, SqlDbType.BigInt);
            updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
            updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
            this.UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd;

        }

        private void LoadUpdateScopeInfoCommand()
        {
            SqlCommand updReplicaInfoCmd = new SqlCommand();
            updReplicaInfoCmd.CommandType = CommandType.Text;
            updReplicaInfoCmd.CommandText = "UPDATE  scope_info SET " +
                                            "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
                                            "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
                                            "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " +
                                            " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 or scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " +
                                            "SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
            updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000);
            updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000);
            updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
            updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
            updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt);
            updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

            this.UpdateScopeInfoCommand = updReplicaInfoCmd;

        }

        private void LoadSelectOverlappingScopesCommand()
        {
            /*SqlCommand overlappingScopesCmd = new SqlCommand();
            overlappingScopesCmd.CommandType = CommandType.Text;
            overlappingScopesCmd.CommandText = @"
   SELECT ScopeTableMap2.table_name AS sync_table_name, 
          ScopeTableMap2.scope_name AS sync_shared_scope_name
   FROM Sync.ScopeTableMap ScopeTableMap1 JOIN Sync.ScopeTableMap ScopeTableMap2
   ON ScopeTableMap1.table_name = ScopeTableMap2.table_name
   AND ScopeTableMap1.scope_name = @sync_scope_name
   WHERE ScopeTableMap2.scope_name <> @sync_scope_name";
            overlappingScopesCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
            this.SelectOverlappingScopesCommand = overlappingScopesCmd;
            */
        }

        private void LoadSelectNewTimestampCommand()
        {
            SqlCommand selectNewTimestampCommand = new SqlCommand();
            string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
            selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
            selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
            selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;

            this.SelectNewTimestampCommand = selectNewTimestampCommand;

        }

        private void LoadSelectTableMaxTimestampsCommand()
        {
            /*SqlCommand selTableMaxTsCmd = new SqlCommand();
            selTableMaxTsCmd.CommandType = CommandType.Text;
            selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " +
                                           "MAX(local_update_peer_timestamp) AS max_timestamp " +
                                           "FROM Customer_Tracking";
            this.SelectTableMaxTimestampsCommand = selTableMaxTsCmd;*/
        }

        private void LoadScopeCongif(string scopeName)
        {
            using (SqlConnection cn = new SqlConnection(Sync.Library.Settings.ConnStringMain))
            {
                cn.Open();

                SqlCommand cm = cn.CreateCommand();
                cm.CommandType = CommandType.Text;
                cm.CommandText = @" SELECT config_data
                                       FROM scope_info
                                         INNER JOIN scope_config
                                         ON scope_info.scope_config_id = scope_config.config_id
                                       WHERE scope_name = @ScopeName";
                cm.Parameters.AddWithValue("@ScopeName", scopeName);

                using (SqlDataReader dr = cm.ExecuteReader())
                {
                    if (dr.Read())
                    {
                        StringReader textReader = new StringReader(dr.GetString(0));
                        XmlSerializer serializer = new XmlSerializer(typeof(SqlSyncProviderScopeConfiguration));
                        _configuration = (SqlSyncProviderScopeConfiguration)serializer.Deserialize(textReader);
                        textReader.Dispose();
                    }
                }
            }
        }

        private SqlSyncProviderScopeConfiguration _configuration;

        private DbSyncAdapter CreateAdapter(Sync.Library.Table table)
        {
            string tableName = table.Name;
            // Create the adapter.
            DbSyncAdapter adapter = new DbSyncAdapter(table.Name,table.RemoteName);

            foreach (Sync.Library.Column col in table.Columns)
            {
                adapter.ColumnMappings.Add(new DbSyncColumnMapping(col.Name,col.RemoteName));
            }

            // Get details on this table
            SqlSyncProviderAdapterConfiguration scopeAdapter = this.GetAdapter(this._configuration.AdapterConfigurations, tableName);
            DbSyncColumnDescription primaryKey = this.GetPrimaryKey(scopeAdapter.Columns);

            // Add in all primary keys - just one.
            if (primaryKey != null)
                adapter.RowIdColumns.Add(primaryKey.UnquotedName);

            // Add stored procedures
            adapter.SelectIncrementalChangesCommand = this.GetSelectIncrementalChangesCommand(scopeAdapter);
            adapter.InsertCommand = this.GetInsertCommand(scopeAdapter);
            adapter.UpdateCommand = this.GetUpdateCommand(scopeAdapter);
            adapter.DeleteCommand = this.GetDeleteCommand(scopeAdapter, primaryKey);
            adapter.SelectRowCommand = this.GetSelectRowCommand(scopeAdapter, primaryKey);
            adapter.InsertMetadataCommand = this.GetInsertMetadataCommand(scopeAdapter, primaryKey);
            adapter.UpdateMetadataCommand = this.GetUpdateMetadataCommand(scopeAdapter, primaryKey);
            adapter.DeleteMetadataCommand = this.GetDeleteMetadataCommand(scopeAdapter, primaryKey);

            return adapter;
        }

        private DbSyncColumnDescription GetPrimaryKey(Collection<DbSyncColumnDescription> columns)
        {
            var a = (from i in columns where i.IsPrimaryKey select i).FirstOrDefault();
            return a;
        }

        private SqlSyncProviderAdapterConfiguration GetAdapter(Collection<SqlSyncProviderAdapterConfiguration> adapters, string tableName)
        {
            // The adapter table names are surrounded by []
            tableName = "[" + tableName.ToLower() + "]";

            foreach (SqlSyncProviderAdapterConfiguration adapter in adapters)
            {
                if (adapter.TableName.ToLower() == tableName)
                    return adapter;
            }
            return null;
        }
                
        private SqlCommand GetInsertCommand(SqlSyncProviderAdapterConfiguration adapter)
        {
            // Define the command: the SP name is in dbo, but is prefixed.
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;

            string commandText = "[dbo]." + adapter.TableName.Trim(new char[]{'[',']'}) + "_insert";
            //command.CommandText = this.ConvertSqlCommandText(commandText, null, this.ObjectPrefix); // no change to schema
            command.CommandText = commandText; // no change to schema

            // Now add all of the fields to the insert
            foreach (DbSyncColumnDescription column in adapter.Columns)
            {
                command.Parameters.Add(column.ParameterName		// e.g. "@P_1" 
                                    , this.GetColumnType(column)
                                    , this.GetColumnSize(column)
                                    , column.UnquotedName		// e.g. "ColumnName1"
                                    );
            }

            command.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

            return command;
        }

        private SqlCommand GetSelectIncrementalChangesCommand(SqlSyncProviderAdapterConfiguration adapter)
        {
            // Define the command: the SP name is in dbo, but is prefixed.
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;

            string commandText = "[dbo]." + adapter.TableName.Trim(new char[] { '[', ']' }) + "_selectchanges";
            //command.CommandText = this.ConvertSqlCommandText(commandText, null, this.ObjectPrefix); // no change to schema
            command.CommandText = commandText; // no change to schema


            command.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
            command.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncScopeRestoreCount, SqlDbType.Int);

            return command;
        }

        private SqlCommand GetUpdateCommand(SqlSyncProviderAdapterConfiguration adapter)
        {
            // Define the command: the SP name is in dbo, but is prefixed.
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;

            string commandText = "[dbo]." + adapter.TableName.Trim(new char[] { '[', ']' }) + "_update";
            //command.CommandText = this.ConvertSqlCommandText(commandText, null, this.ObjectPrefix); // no change to schema
            command.CommandText = commandText; // no change to schema

            // Now add all of the fields to the insert
            foreach (DbSyncColumnDescription column in adapter.Columns)
            {
                command.Parameters.Add(column.ParameterName		// e.g. "@P_1" 
                                    , this.GetColumnType(column)
                                    , this.GetColumnSize(column)
                                    , column.UnquotedName		// e.g. "ColumnName1"
                                    );
            }

            command.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
            command.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

            return command;
        }

        private SqlCommand GetDeleteCommand(SqlSyncProviderAdapterConfiguration adapter,DbSyncColumnDescription primaryKey)
        {
            // Define the command: the SP name is in dbo, but is prefixed.
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;

            string commandText = "[dbo]." + adapter.TableName.Trim(new char[] { '[', ']' }) + "_delete";
            //command.CommandText = this.ConvertSqlCommandText(commandText, null, this.ObjectPrefix); // no change to schema
            command.CommandText = commandText; // no change to schema

            // Now add all of the fields to the insert

            command.Parameters.Add("@" + primaryKey.ParameterName,this.GetColumnType(primaryKey),GetColumnSize(primaryKey),primaryKey.UnquotedName);
            command.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
            command.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

            return command;
        }

        private SqlCommand GetSelectRowCommand(SqlSyncProviderAdapterConfiguration adapter, DbSyncColumnDescription primaryKey)
        {
            // Define the command: the SP name is in dbo, but is prefixed.
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;

            string commandText = "[dbo]." + adapter.TableName.Trim(new char[] { '[', ']' }) + "_selectrow";
            //command.CommandText = this.ConvertSqlCommandText(commandText, null, this.ObjectPrefix); // no change to schema
            command.CommandText = commandText; // no change to schema

            // Now add all of the fields to the insert
            /*foreach (DbSyncColumnDescription column in adapter.Columns)
            {
                command.Parameters.Add(column.ParameterName		// e.g. "@P_1" 
                                    , this.GetColumnType(column)
                                    , this.GetColumnSize(column)
                                    , column.UnquotedName		// e.g. "ColumnName1"
                                    );
            }*/
            command.Parameters.Add("@" + primaryKey.ParameterName, this.GetColumnType(primaryKey), GetColumnSize(primaryKey), primaryKey.UnquotedName);
            command.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncScopeRestoreCount, SqlDbType.Int);

            return command;
        }

        private SqlCommand GetInsertMetadataCommand(SqlSyncProviderAdapterConfiguration adapter, DbSyncColumnDescription primaryKey)
        {
            // Define the command: the SP name is in dbo, but is prefixed.
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;

            string commandText = "[dbo]." + adapter.TableName.Trim(new char[] { '[', ']' }) + "_insertmetadata";
            //command.CommandText = this.ConvertSqlCommandText(commandText, null, this.ObjectPrefix); // no change to schema
            command.CommandText = commandText; // no change to schema

            // Now add all of the fields to the insert

            command.Parameters.Add("@" + primaryKey.ParameterName, this.GetColumnType(primaryKey), GetColumnSize(primaryKey), primaryKey.UnquotedName);
            command.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
            command.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
            command.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
            command.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

            return command;
        }

        private SqlCommand GetUpdateMetadataCommand(SqlSyncProviderAdapterConfiguration adapter, DbSyncColumnDescription primaryKey)
        {
            // Define the command: the SP name is in dbo, but is prefixed.
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;

            string commandText = "[dbo]." + adapter.TableName.Trim(new char[] { '[', ']' }) + "_updatemetadata";
            //command.CommandText = this.ConvertSqlCommandText(commandText, null, this.ObjectPrefix); // no change to schema
            command.CommandText = commandText; // no change to schema


            command.Parameters.Add("@" + primaryKey.ParameterName, this.GetColumnType(primaryKey), GetColumnSize(primaryKey), primaryKey.UnquotedName);
            command.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
            command.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
            command.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
            command.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

            return command;
        }

        private SqlCommand GetDeleteMetadataCommand(SqlSyncProviderAdapterConfiguration adapter, DbSyncColumnDescription primaryKey)
        {
            // Define the command: the SP name is in dbo, but is prefixed.
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;

            string commandText = "[dbo]." + adapter.TableName.Trim(new char[] { '[', ']' }) + "_deletemetadata";
            //command.CommandText = this.ConvertSqlCommandText(commandText, null, this.ObjectPrefix); // no change to schema
            command.CommandText = commandText; // no change to schema


            command.Parameters.Add("@" + primaryKey.ParameterName, this.GetColumnType(primaryKey), GetColumnSize(primaryKey), primaryKey.UnquotedName);
            command.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
            command.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
            command.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

            return command;
        }

        private SqlDbType GetColumnType(DbSyncColumnDescription column)
        {
            return (SqlDbType)Enum.Parse(typeof(SqlDbType), column.Type,true);
        }

        private int GetColumnSize(DbSyncColumnDescription column)
        {
            return int.Parse(column.Size ?? "0");
        }

    }
}
